"""
Case Type   : mysql protocol compatibility
Case Name   : 协议兼容下验证show语法支持2
Create At   : 2024/10/23
Owner       : lvlintao666
Description :
    1、设置协议兼容相关配置
    2、建表
    3、创建用户
    4、授权
    5、回收权限
    6、show权限
    7、清理环境
Expect      :
    1、配置成功
    2、建表成功
    3、创建成功
    4、授权成功
    5、回收成功
    6、show成功
    7、清理成功
History     :
"""

import os
import time
import unittest

from yat.test import Node
from yat.test import macro

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.CommonMySQL import CommonMySQL
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class ProtoTestCase(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.com = Common()
        self.comsh = CommonSH('PrimaryDbUser')
        self.pri_node = Node('PrimaryDbUser')
        self.my_node = Node('mysql5')
        self.db_name = "proto_test_db"
        self.conf_path = os.path.join(macro.DB_INSTANCE_PATH,
                                      'postgresql.conf')
        self.create_db = f"drop database if exists {self.db_name};" \
                         f"create database {self.db_name} dbcompatibility='B'"
        self.user = f"proto_test_29"
        self.password = f"proto_test123"
        self.create_u = f"drop user if exists {self.user} cascade;" \
                        f"create user {self.user} with password '{self.password}';" \
                        f"select set_native_password('{self.user}','{self.password}','');" \
                        f"grant all privileges to {self.user};"
        self.show_cmd = f"show enable_dolphin_proto;" \
                        f"show dolphin_server_port;"
        self.c_user = f"drop user if exists u_protocol_0029;" \
                      f"create user u_protocol_0029 identified by 'Test@123';"
        self.c_tb = f"drop table if exists tab_protocol_0029; " \
                    f"create table tab_protocol_0029( id tinyint, " \
                    f"name varchar(100), score tinyint); "
        self.grant = f"grant select,insert,update on tab_protocol_0029 to u_protocol_0029;" \
                     f"show grants for u_protocol_0029;"
        self.revoke = f"revoke insert,update on tab_protocol_0029 from u_protocol_0029;" \
                      f"show grants for u_protocol_0029;"
        self.show_var = f"show variables like 'audit%logout';" \
                        f"show session_timeout;" \
                        f"show processlist;" \
                        f"flush binary logs;"
        self.clean_tb = f"drop table if exists tab_protocol_0029 cascade constraints;" \
                        f"drop user if exists u_protocol_0029 cascade;" \
                        f"drop user if exists {self.user} cascade;"
        self.clean_db = f"drop database {self.db_name};"

    def test_index(self):
        text = '--step1:设置协议兼容相关配置;expect:创建成功--'
        logger.info(text)
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       'enable_dolphin_proto = on')
        logger.info(msg)
        my_port = int(self.pri_node.db_port) + 10
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       f'dolphin_server_port = {my_port}')
        logger.info(msg)
        sed_cmd = f"sed -i '$a dolphin.default_database_name={self.db_name}' " \
                  f"{self.conf_path}"
        logger.info(sed_cmd)
        res = self.com.get_sh_result(self.pri_node, sed_cmd)
        logger.info(res)

        msg = self.comsh.restart_db_cluster()
        logger.info(msg)
        show_res = self.comsh.execut_db_sql(self.show_cmd)
        logger.info(show_res)
        self.assertIn('on', show_res, '执行失败' + text)
        self.assertIn(f'{my_port}', show_res, '执行失败' + text)

        logger.info(self.create_db)
        db_res = self.comsh.execut_db_sql(self.create_db)
        logger.info(db_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, db_res,
                      '执行失败' + text)
        logger.info(self.create_u)
        u_res = self.comsh.execut_db_sql(self.create_u, dbname=self.db_name)
        logger.info(u_res)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, u_res,
                      '执行失败' + text)

        text = '--step2:M*连接数据库建表插入数据;expect:创建成功--'
        logger.info(text)
        my_sh = CommonMySQL('mysql5')
        c_res = my_sh.execut_db_sql(sql=self.c_tb, dbname=self.db_name, host=self.pri_node.db_host,
                                    port=my_port, user=self.user, password=self.password)
        logger.info(c_res)
        self.assertNotIn('ERROR', c_res, '执行失败' + text)

        text = '--step3:M*连接数据库创建用户;expect:创建成功--'
        logger.info(text)
        c_res = my_sh.execut_db_sql(sql=self.c_user, dbname=self.db_name, host=self.pri_node.db_host,
                                    port=my_port, user=self.user, password=self.password)
        logger.info(c_res)
        self.assertNotIn('ERROR', c_res, '执行失败' + text)

        text = '--step4:M*连接数据库授权;expect:创建成功--'
        logger.info(text)
        grant_res = my_sh.execut_db_sql(sql=self.grant, dbname=self.db_name, host=self.pri_node.db_host,
                                        port=my_port, user=self.user, password=self.password)
        logger.info(grant_res)
        self.assertNotIn('ERROR', grant_res, '执行失败' + text)

        text = '--step5:M*连接数据库回收权限;expect:创建成功--'
        logger.info(text)
        revoke_res = my_sh.execut_db_sql(sql=self.revoke, dbname=self.db_name, host=self.pri_node.db_host,
                                         port=my_port, user=self.user, password=self.password)
        logger.info(revoke_res)
        self.assertNotIn('ERROR', revoke_res, '执行失败' + text)

        text = '--step6:M*连接数据库show相关查询;expect:创建成功--'
        logger.info(text)
        show_res = my_sh.execut_db_sql(sql=self.show_var, dbname=self.db_name, host=self.pri_node.db_host,
                                       port=my_port, user=self.user, password=self.password)
        logger.info(show_res)
        self.assertNotIn('ERROR', show_res, '执行失败' + text)

    def tearDown(self):
        text = '--step4:清理环境;expect:成功--'
        logger.info(text)
        clean_res = self.comsh.execut_db_sql(self.clean_tb, dbname=self.db_name)
        logger.info(clean_res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, clean_res,
                      '执行失败' + text)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, clean_res,
                      '执行失败' + text)
        clean_res = self.comsh.execut_db_sql(self.clean_db)
        logger.info(clean_res)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, clean_res,
                      '执行失败' + text)
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       'enable_dolphin_proto = off')
        logger.info(msg)
        self.assertTrue(msg, '执行失败' + text)
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       f'dolphin_server_port = 3308')
        logger.info(msg)
        self.assertTrue(msg, '执行失败' + text)
        sed_cmd = f"sed -i '/dolphin.default_database_name={self.db_name}/d' " \
                  f"{self.conf_path}"
        logger.info(sed_cmd)
        res = self.com.get_sh_result(self.pri_node, sed_cmd)
        logger.info(res)
        msg = self.comsh.restart_db_cluster()
        logger.info(msg)
        status = self.comsh.get_db_cluster_status()
        logger.info(status)
        self.assertTrue("Degraded" in status or "Normal" in status)
        logger.info(f'-----{os.path.basename(__file__)} end-----')
